﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spPostDeploy_LookupTable_SqlVersions]
#-- Purpose:		To be called by a process after the TFS Build has completed.
#--					Updates the tblScripts lookup table to be the latest values.
#--	Last Update:	04/17/2013
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spPostDeploy_LookupTable_MsxConfiguration]
AS

SET NOCOUNT ON

IF EXISTS (SELECT TOP 1 * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tblMSX_configuration')
  BEGIN
	--- Define Local Variables
	DECLARE @qrySqlServerInventory nvarchar(MAX)

	--- Initialize Local Variables
	SELECT @qrySqlServerInventory	= '--- Declare Local Variables
	DECLARE @backup_directory_name varchar(4000), @service_name nvarchar(255), @sql_service nvarchar(255), @agent_service nvarchar(255)

	--- Get the Backup Directory
	EXEC [master].[dbo].xp_instance_regread N''HKEY_LOCAL_MACHINE'', N''Software\Microsoft\MSSQLServer\MSSQLServer'', N''BackupDirectory'', @backup_directory_name OUTPUT
	SELECT	@backup_directory_name = LTRIM(RTRIM(@backup_directory_name))
	
	--- Ensure Backup Directory ends with a ''\''
	IF NOT RIGHT(@backup_directory_name, 1) = ''\''
		SELECT @backup_directory_name = @backup_directory_name + ''\''

	--- Return the server information
	SELECT		SERVERPROPERTY(''ServerName'') server_name, 
				SERVERPROPERTY(''EngineEdition'') engine_edition, 
				(CASE	WHEN SERVERPROPERTY(''EditionID'') IS NOT NULL THEN SERVERPROPERTY(''EditionID'')
						WHEN SERVERPROPERTY(''EngineEdition'') = 1 THEN -1253826760
						WHEN SERVERPROPERTY(''EngineEdition'') = 2 THEN -1534726760
						WHEN SERVERPROPERTY(''EngineEdition'') = 3 THEN 1804890536
						ELSE 0
						END) edition_id, 
				SERVERPROPERTY(''ProductVersion'') product_version, 
				SERVERPROPERTY(''ProductLevel'') product_level,
				ISNULL(CAST(CAST(SUM(CASE WHEN sc.name = ''Jobs from MSX'' THEN 1 ELSE 0 END) AS bit) as int), 0) has_master_jobs,
				ISNULL(CAST(CAST(SUM(CASE WHEN sc.name = ''Jobs from MSX'' THEN 0 ELSE 1 END) AS bit) as int), 0) has_local_jobs,
				@backup_directory_name backup_directory,
				(CASE	WHEN ISNULL(CAST(CAST(SERVERPROPERTY(''IsIntegratedSecurityOnly'') AS bit) as int), 0) = 0 THEN 1
						ELSE 0
						END) is_mixed_mode,
				(CASE	WHEN EXISTS ( SELECT TOP 1 * FROM [master].[dbo].sysdatabases WHERE name = ''$(DatabaseName)'' ) THEN 1 ELSE 0 END) has_centraldba_db
	FROM		[msdb].[dbo].sysjobs sj
	JOIN		[msdb].[dbo].syscategories sc ON sj.category_id = sc.category_id
	WHERE		sj.enabled = 1'

	--- Default Values
	DECLARE @t TABLE ([cfg_key] NVARCHAR(50), [cfg_value] NVARCHAR(MAX))
	INSERT INTO @t ([cfg_key], [cfg_value])
		SELECT 'AdminEmailAccount', '$(AdminEmailAccount)'
		UNION SELECT 'AdminEmailDomainName', '$(AdminEmailDomainName)'
		UNION SELECT 'AdminSmtpServer', '$(AdminSmtpServer)'
		UNION SELECT 'SqlServiceAccountDomainGroup', '$(SqlServiceAccountDomainGroup)'
		UNION SELECT 'qrySqlServerInventory', @qrySqlServerInventory

	--- Insert Missing Records
	INSERT INTO [dbo].[tblMSX_configuration] ([cfg_key], [cfg_value])
		SELECT		t.cfg_key,
					t.cfg_value
		FROM		@t t
		LEFT JOIN	[dbo].[tblMSX_configuration] c ON c.cfg_key = t.cfg_key
		WHERE		c.cfg_key IS NULL
  END

SET NOCOUNT OFF